﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using SqlserverHelper;
using System.Threading;

using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
using OJ;
using DLLmySQL;

/// <summary>
/// 实时查询
/// </summary>
public partial class getDataSell : System.Web.UI.Page
{

    public string strCnn = ConfigurationSettings.AppSettings["DataBaseString"];
    protected void Page_Load(object sender, EventArgs e)
    {
        string customer_code = "";
        string shop_code = "";
        string prd_code = "";
        string psi_dist = "0";
        string flag = "0";
        string seller_code = "";
        string user_identity = "1";
        // 客户
        if (Request.Form["customer_code"] != null && Request.Form["customer_code"].ToString().Trim() != "")
        {
            customer_code = Request.Form["customer_code"].ToString();
        }

        // 门店
        if (Request.Form["shop_code"] != null && Request.Form["shop_code"].ToString().Trim() != "")
        {
            shop_code = Request.Form["shop_code"].ToString();
        }

        // 产品
        if (Request.Form["prd_code"] != null && Request.Form["prd_code"].ToString().Trim() != "")
        {
            prd_code = Request.Form["prd_code"].ToString();
        }

        if (Request.Form["psi_dist"] != null && Request.Form["psi_dist"].ToString().Trim() != "")
        {
            psi_dist = Request.Form["psi_dist"].ToString();
        }

        

        // 查询数据（0-当天top5，1-前一天top5数据）
        if (Request.Form["flag"] != null && Request.Form["flag"].ToString().Trim() != "")
        {
            flag = Request.Form["flag"].ToString();
        }

        // 收银员
        if (Request.Form["seller_code"] != null && Request.Form["seller_code"].ToString().Trim() != "")
        {
            seller_code = Request.Form["seller_code"].ToString();
        }

        // 用户身份权限
        if (Request.Form["user_identity"] != null && Request.Form["user_identity"].ToString().Trim() != "")
        {
            user_identity = Request.Form["user_identity"].ToString();
        }

        string stResult = f_sell(customer_code, shop_code, prd_code, psi_dist, flag, seller_code, user_identity);
        Response.Write(stResult);
    }

    public string f_sell(string customer_code, string shop_code, string prd_code,  string psi_dist, string flag, string seller_code, string user_identity)
    {
        Define.retSellList ret = new Define.retSellList();
        List<Define.SellList> L = new List<Define.SellList>();
        List<Define.lineChartData> L1 = new List<Define.lineChartData>();
        List<Define.top5> L2 = new List<Define.top5>();
        try
        {
            // 1
            string strQuery = @"SELECT id,ymd,prd_code,prd_name,prd_spec,mfrs,prd_unit,tax_rate,retail_price,sell_qty,sell_price,sell_amt,dept_code,dept_name,dept_addr,order_id,order_line_id,prd_batch,period,pzwh,seller_code,seller_name,made_time,maker,updater_time,updater 
                                FROM dsn_sell WHERE 1=1 
                                and dept_name  =  '" + shop_code.Trim() + "' and prd_code = '" + prd_code.Trim() + "'";
            LogBLL.log("【实时查询】【查询1】" + strQuery);
            DataTable DT = CmySQL.GetDataTbale(strCnn, strQuery);

            
            if (DT != null && DT.Rows.Count > 0)
            {
                
                for (int i = 0; i < DT.Rows.Count; i++)
                {
                    Define.SellList r = new Define.SellList();
                    r.id = DT.Rows[i][0].ToString().Trim();
                    r.ymd = DT.Rows[i][1].ToString().Trim();
                    r.prd_code = DT.Rows[i][2].ToString().Trim();
                    r.prd_name = DT.Rows[i][3].ToString().Trim();
                    r.prd_spec = DT.Rows[i][4].ToString().Trim();
                    r.mfrs = DT.Rows[i][5].ToString().Trim();
                    r.prd_unit = DT.Rows[i][6].ToString().Trim();
                    r.tax_rate = DT.Rows[i][7].ToString().Trim();
                    r.retail_price = DT.Rows[i][8].ToString().Trim();
                    r.sell_qty = DT.Rows[i][9].ToString().Trim();
                    r.sell_price = DT.Rows[i][10].ToString().Trim();
                    r.sell_amt = DT.Rows[i][11].ToString().Trim();
                    r.dept_code = DT.Rows[i][12].ToString().Trim();
                    r.dept_name = DT.Rows[i][13].ToString().Trim();
                    r.dept_addr = DT.Rows[i][14].ToString().Trim();
                    r.order_id = DT.Rows[i][15].ToString().Trim();
                    r.order_line_id = DT.Rows[i][16].ToString().Trim();
                    r.prd_batch = DT.Rows[i][17].ToString().Trim();
                    r.period = DT.Rows[i][18].ToString().Trim();
                    r.pzwh = DT.Rows[i][19].ToString().Trim();
                    r.seller_code = DT.Rows[i][20].ToString().Trim();
                    r.seller_name = DT.Rows[i][21].ToString().Trim();
                    r.made_time = DT.Rows[i][22].ToString().Trim();
                    r.maker = DT.Rows[i][23].ToString().Trim();
                    r.updater_time = DT.Rows[i][24].ToString().Trim();
                    r.updater = DT.Rows[i][25].ToString().Trim();

                    L.Add(r);
                }

                

            }

            // 2
            //查询lineChartData (近一个月数据)
            //String strQuery1 = "SELECT DATE_FORMAT(ymd,'%Y-%m-%d'),SUM(sell_qty) qty  FROM dsn_sell WHERE  ymd BETWEEN DATE_SUB(NOW(),INTERVAL 1 MONTH) AND NOW() ";
            String strQuery1 = String.Format(
                               @"   SELECT DATE_FORMAT(ymd,'%Y-%m-%d'),SUM(sell_qty) qty  
                                    FROM dsn_sell 
                                    WHERE  1=1 
                                    and from_id = '{0}' 
                                    and ymd >= DATE_SUB(NOW(),INTERVAL 1 MONTH)
                                    and ymd <= NOW()
                                    ", user_identity);
            

            if (shop_code != "")
            {
                strQuery1 = strQuery1 + " and dept_name like '%" + shop_code.Trim() + "%'";               
            }
            if (prd_code != "")
            {
                strQuery1 = strQuery1 + " and prd_code = '" + prd_code.Trim() + "'";
            }
            if (seller_code != "")
            {
                strQuery1 = strQuery1 + " and seller_code = '" + seller_code.Trim() + "'";
            }

            // 销售
            if (psi_dist == "20")
            {
                strQuery1 = strQuery1 + " and sell_qty >=0 ";
            }
            // 销售退货
            if (psi_dist == "21")
            {
                strQuery1 = strQuery1 + " and sell_qty < 0 ";
            }


            

            strQuery1 = strQuery1 + " GROUP BY DATE_FORMAT(ymd,'%Y-%m-%d')";
            LogBLL.log("【实时查询】【查询lineChartData (近一个月数据)】" + strQuery1);
            DataTable DT1 = CmySQL.GetDataTbale(strCnn, strQuery1);

            if (DT1 != null && DT1.Rows.Count > 0)
            {
                for (int i = 0; i < DT1.Rows.Count; i++)
                {
                    Define.lineChartData r1 = new Define.lineChartData();
                    r1.name = DT1.Rows[i][0].ToString().Trim();
                    r1.data = DT1.Rows[i][1].ToString().Trim();

                    L1.Add(r1);
                }
            }


            //查询top5
            String strQuery2 = "";
            string strUserIdentity = " and from_id = '"+ user_identity + "' ";
            if (flag == "1")
            {
                strQuery2 =     " SELECT prd_name,SUM(sell_qty) sumQty FROM dsn_sell WHERE 1=1 ";
                strQuery2 +=    " and from_id = '" + user_identity + "' ";
                strQuery2 +=    " and ymd >= CURDATE()-1 AND ymd < CURDATE() "; // 查询前一天top5数据
            }else {
                strQuery2 = " SELECT prd_name,SUM(sell_qty) sumQty FROM dsn_sell WHERE 1=1 " +
                            " and from_id = '" + user_identity + "' " +
                            " and ymd >= CURDATE() AND ymd < CURDATE()+1 "; // 查询当天top5数据
            }
             
            if (shop_code != "")
            {
                strQuery2 = strQuery2 + " and dept_name like '%" + shop_code.Trim() + "%'"; 
            }
            /*if (prd_code != "")
            {
                strQuery2 = strQuery2 + " and prd_code = '" + prd_code.Trim() + "'";
            }*/
            if (seller_code != "")
            {
                strQuery2 = strQuery2 + " and seller_code = '" + seller_code.Trim() + "'";
            }
            strQuery2 = strQuery2 + " GROUP BY prd_code";
            //LogBLL.log(strQuery2);
            LogBLL.log("【实时查询】【查询top5】" + strQuery2);
            DataTable DT2 = CmySQL.GetDataTbale(strCnn, strQuery2);

            if (DT2 != null && DT2.Rows.Count > 0)
            {
                L2 = new List<Define.top5>();
                for (int i = 0; i < DT2.Rows.Count; i++)
                {
                    Define.top5 r2 = new Define.top5();
                    r2.name = DT2.Rows[i][0].ToString().Trim()+"(" + DT2.Rows[i][1].ToString().Trim() + ")";
                    r2.data = int.Parse( DT2.Rows[i][1].ToString().Trim());

                    L2.Add(r2);
                }
            }


            ret.code = 200;
            ret.msg = "success";

            ret.data = null;
            ret.lineChartData = L1;
            ret.top5Data = L2;

            OperLog.log("实时查询-查询", user_identity);

        }
        catch
        {
            ret.code = 20000;
            ret.msg = "服务不可用";
            ret.data = null;
            ret.lineChartData = null;
            ret.top5Data = null;
        }

        return Ojson.ObjectToJson(ret);
    }
}